﻿Public Class Form4
    Dim da As New Class1
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim ds2 As New DataSet
    Dim dt2 As New DataTable
    Dim strSql As String = ""
    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Location = New Point(Form1.Location.X + Form1.Width / 5, Form1.Location.Y + Form1.Height / 5)
        Form1.Enabled = False
        strSql = "select * from video where video_status='Available' order by video_title"
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        If dt.Rows.Count <> 0 Then
            For i = 0 To dt.Rows.Count - 1
                ComboBox1.Items.Add(dt.Rows(i)("video_title"))
            Next
        Else
            MsgBox("ไม่มีในคลัง")
            Me.Close()
        End If
        strSql = "select * from customer where cus_permission > cus_num order by cus_name"
        ds2 = da.PopulateList(strSql)
        dt2 = ds2.Tables(0)
        If dt2.Rows.Count <> 0 Then
            For i = 0 To dt2.Rows.Count - 1
                ComboBox2.Items.Add(dt2.Rows(i)("cus_name"))
            Next
        Else
            MsgBox("ไม่สามารถยืมได้")
            Me.Close()
        End If

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub
    Private Sub Form4_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Form1.Enabled = True
        strSql = "select r.rent_id,b.cus_name,r.rent_pick,r.rent_return,v.video_title from rental as r join video as v on r.video_id = v.video_id join customer as b on r.cus_id = b.cus_id order by rent_id"
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)

        strSql = "select * from customer "
        Form1.DataGridView2.DataSource = dt
        Form1.DataGridView2.AllowUserToResizeRows = False
        For Each column As DataGridViewColumn In Form1.DataGridView2.Columns
            Select Case column.HeaderText
                Case "rent_id"
                    column.HeaderText = "เลขทะเบียน"
                Case "cus_name"
                    column.HeaderText = "ชื่อสมาชิก"
                Case "rent_pick"
                    column.HeaderText = "วันยืม"
                Case "rent_return"
                    column.HeaderText = "วันคืน"
                Case "video_title"
                    column.HeaderText = "ชื่อเรื่อง"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            End Select
        Next
        Form1.DataGridView2.Show()
        If dt.Rows.Count <> 0 Then
            strSql = "select * from customer where cus_name='" & Form1.DataGridView2.SelectedRows(0).Cells(1).Value & "' "
            ds2 = da.PopulateList(strSql)
            dt2 = ds2.Tables(0)
            If dt2.Rows.Count <> 0 Then
                Form1.TextBox1.Text = dt2.Rows(0)("cus_id")
                Form1.TextBox2.Text = dt2.Rows(0)("cus_name")
                Form1.TextBox3.Text = dt2.Rows(0)("cus_sname")
                Form1.TextBox4.Text = dt2.Rows(0)("cus_permission")
                Form1.TextBox6.Text = dt2.Rows(0)("cus_num")
                Form1.TextBox7.Text = dt2.Rows(0)("cus_permission") - dt2.Rows(0)("cus_num")
            End If
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If String.IsNullOrEmpty(ComboBox1.Text) = True Then
            MsgBox("กรุณาเลือกวิดีโอ")
            Exit Sub
        End If
        If String.IsNullOrEmpty(ComboBox2.Text) = True Then
            MsgBox("กรุณาเลือกสมาชิก")
            Exit Sub
        End If
        strSql = "select * from customer where cus_name='" & ComboBox2.Text & "' "
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        If dt.Rows.Count <> 0 Then
            strSql = "Update customer set cus_num='" & dt.Rows(0)("cus_num") + 1 & "' where cus_name='" & ComboBox2.Text & "'"
            da.PopulateList(strSql)
        Else
            MsgBox("ไม่มีสมาชิกชื่อนี้")
            Exit Sub
        End If
        strSql = "select * from video where video_title='" & ComboBox1.Text & "' "
        ds2 = da.PopulateList(strSql)
        dt2 = ds2.Tables(0)
        If dt.Rows.Count <> 0 Then
            strSql = "Update video set video_status='Busy' where video_title='" & ComboBox1.Text & "'"
            da.PopulateList(strSql)
        Else
            MsgBox("ไม่มีวิดีโอดังกล่าว")
            Exit Sub
        End If
        Dim t1 As String = Date.Now.Year & "-" & Date.Now.Month & "-" & Date.Now.Day
        Dim t2 As String = Date.Now.Year & "-" & Date.Now.Month & "-" & Date.Now.Day + 3
        strSql = "insert into rental(cus_id,video_id,rent_pick,rent_return) values('" & dt.Rows(0)("cus_id") & "','" & dt2.Rows(0)("video_id") & "','" & t1 & "','" & t2 & "')"
        da.PopulateList(strSql)
        MsgBox("ทำการยืมวิดีโอเรียบร้อยแล้ว")
        Me.Close()
    End Sub

End Class